<?php
/**
 * 数据库连接类
 */
class pdoNew
{
    private static $__PDO = null;
    private static $__PDOS = null;
    private static $fetchType = PDO::FETCH_ASSOC;
    private static $__instance = [];
    private $_where = ' 1=1 ';
    private $_field = '*';
    private $_order = '';
    private $_limit = '';
    public $_table = '';
    public $_join  = '';
    public $_sql   = '';
    public $_group = '';
    
    /**
     * 初始化数据库连接
     */
    function __construct($db_data)
    {
        $alias = $db_data['alias'];
        //获取数据
        if(isset(self::$__PDOS[$alias]))
        {
            return self::$__PDOS[$alias];
        }
        else
        {
            try
            {
                if($db_data['per'])
                {
                    $dsn = $db_data['type'] . ":host=" . $db_data['server'] . ";port=" . $db_data['port'] . ";dbname=" . $db_data['db_name'] . ";charset=" . $db_data['charset'];
                    self::$__PDOS[$alias] = self::$__PDO = new PDO($dsn, $db_data['user_name'], $db_data['user_pw'], "array(PDO::ATTR_PERSISTENT => true)");
                }
                else
                {
                    $dsn = $db_data['type'] . ":host=" . $db_data['server'] . ";port=" . $db_data['port'] . ";dbname=" . $db_data['db_name'] . ";charset=" . $db_data['charset'];
                    self::$__PDOS[$alias] = self::$__PDO = new PDO($dsn, $db_data['user_name'], $db_data['user_pw']);
                }
                if($db_data['charset'])
                {
                    //self::$__PDO->exec("set names " . DB_CHARSET);
                }
                return self::$__PDOS[$alias];
            }
            catch(PDOException $e)
            {
               throw new PDOException($e);
            }
        }
    }

    public function __get($name){


    }
    /**
     * setFetchType(PDO::FETCH_OBJ);
     */
    public static function setFetchType($type)
    {
        self::$fetchType = $type;
        // setFetchMode(PDO::FETCH_ASSOC);
    }
    
    /**
     * 静态方法，单例模式返回数据库连接实例，
     */
    public static function getInstance()
    {
        //echo 213;
        // if(!isset(self::$__instance[$alias]))
        // {
        //     self::$__instance[$alias] = new self($db_data[$alias]);
        // }
        // return self::$__instance[$alias];
    }
    
    /**
     * 返回一条数据的stmt
     */
    public static function fetch($sql, $params = null)
    {
        $stmt = self::execute($sql, $params);
        if($stmt)
        {
            return $stmt->fetch(self::$fetchType);
        }
    }
    
    /**
     * 返回多条数据
     */
    public static function fetchAll($sql, $params = null)
    {  
        $stmt = self::execute($sql, $params);
        if($stmt)
        {
            return $stmt->fetchAll(self::$fetchType);
        }
    }
    
    /**
     * 返回一条数据
     *
     * @return
     *
     */
    public static function fetchColumn($sql, $params = null)
    {
        $stmt = self::execute($sql, $params);
        if($stmt)
        {
            return $stmt->fetchColumn();
        }
    }
  
    /**
     * 执行查询
     */
    public static function query($sql, $params = null)
    {
        $stmt = self::execute($sql, $params);
        return $stmt;
    }
    
    /**
     *
     * @param <type> $sql
     *            语句
     * @param <type> $params
     *            参数数组
     * @return <type> $stmt 返回
     */
    private static function execute($sql, $params)
    {
        //self::getInstance();
        try
        {
            $stmt = self::$__PDO->prepare($sql);
            if ($stmt === false)
            {
                return false;
            }
            if($params !== null)
            {
                if(is_array($params))
                {
                    $arrKey = array_keys($params);
                    foreach($arrKey as $key)
                    {
                        $stmt->bindParam($key, $params[$key]);
                    }
                }
            }
            
            if($stmt->execute())
            {
                return $stmt;
            }
            else
            {
                return false;
            }
        }
        catch(PDOException $e)
        {
            throw new PDOException($e);
        }
    }
    
    /**
     * 关闭数据库连接
     */
    public static function close()
    {
        self::$__PDO == null;
    }
    /**
     * 获取where条件
     * @access public
     * @param mixed $where 搜索条件 支持array string
     * @author zly 08/30
     * @return obj
     */
    public function where($where){
        $this->_where = $this->formatWhere($where);
        return $this;
    }
     /**
     * 获取where条件
     * @access public
     * @param mixed $where 搜索条件 支持array string
     * @author zly 08/30
     * @return obj
     */
    public function formatWhere($where,$con=' AND '){
            if(is_string($where)){
        return $where;
    }
    $res = array();
    if($con == ' AND '){
        $res[] = '1=1';
    }
    $replace = array(
        'eq'  => ' = ',
        'elt' => ' <= ',
        'egt' => " >= ",
        'lt'  => ' < ',
        'gt'  => ' > ',
        'neq' => ' != ',
    );
    $orSql = '';
    foreach($where as $key => $val){
        if(strpos($key, '.')){
            $key = str_replace('.', '.`', $key).'`';
        }else{
            if(is_numeric($key)){

            }else{
                $key = '`'.$key.'`';
            }
        }
        if(is_array($val)){
            $pix = $val[0];
            if(isset($replace[$pix])){
                $pix = $replace[$pix];
                if(is_numeric($val[1])){
                    $res[] = "{$key} {$pix} {$val[1]}";
                }else{
                    $res[] = "{$key} {$pix} '{$val[1]}'";
                }
                
            }
            if($pix == 'in' || $pix == 'not in'){
                $val[1] = dealInData($val[1]);
                if(is_array($val[1])){
                    $val[1] = implode(',',$val[1]);
                }
                $res[] = "$key $pix ({$val[1]})"; 
            }
            if($pix == 'between'){
                $res[] = "$key between '{$val[1]}' and '{$val[2]}'";
            }
            if($pix == 'like'){
                $res[] = "$key like '{$val[1]}'";
            }
        }else{
            if(is_numeric($key)){
                if($val){
                    $res[] = $val;
                }
            }else{
                $val = $val;
                if(is_numeric($val)){
                    $res[] = "$key = $val";
                }else{
                    $res[] = "$key = '$val'";   
                } 
            }
            
        }
    }
    if(!empty($res) || $orSql){
        return implode($con, $res).$orSql;
    }else{
        return '';
    }
    }
    /**
     * 获取field字段
     * @access public
     * @param mixed $field 字段 支持array string
     * @author zly 08/30
     * @return obj
     */
    public function field($field='*'){
        if(is_array($field)){
            $field = implode(',', $field);
        }
        $this->_field = $field;
        return $this;
    }
    /**
     * 获取limit字段
     * @access public
     * @param mixed $limit 字段 string
     * @author zly 08/30
     * @return obj
     */
    public function limit($limit = ''){
        if($limit){
            $this->_limit = " limit ".$limit;
        }
        return $this;
    }
    /**
     * 支持order排序
     * @access public
     * @param mixed $order 字段 string
     * @author zly 08/30
     * @return obj
     */
    public function order($order){
        if($order){
            $this->_order = ' order by '.$order;
        }
        return $this;
    }
    /**
     * 支持order排序
     * @access public
     * @param mixed $order 字段 string
     * @author zly 08/30
     * @return obj
     */
    public function group($group){
        if($group){
            $this->_group = ' group by '.$group;
        }
        return $this;
    }
    /**
     * 获取一条数据
     * @access public
     * @param mixed $sql 字段 string
     * @author zly 08/30
     * @return obj
     */
    public function find($sql=''){
        if($sql){
            return self::fetch($sql);
        }else{
            $sql = "select ".$this->_field." from ".$this->_table.$this->_join." where ".$this->_where.' '.$this->_group.$this->_order." limit 1";
            $this->_sql = $sql;
            return self::fetch($sql);
        }
        
    }
    /**
     * 获取多条数据
     * @access public
     * @param mixed $sql 字段 string
     * @author zly 08/30
     * @return obj
     */
    public function select($sql=''){
        if($sql){
            return self::fetchAll($sql);
        }else{
            $sql = "select ".$this->_field." from ".$this->_table.$this->_join." where ".$this->_where.' '.$this->_group.$this->_order.' '.$this->_limit;
            $this->_sql = $sql;
            
            return self::fetchAll($sql);
        }
        
    }
    /**
     * 获取数据的条数
     * @access public
     * @param mixed $sql 字段 string
     * @author zly 08/30
     * @return obj
     */
    public function count(){
        $sql = "select count(*) as num from ".$this->_table.$this->_join." where ".$this->_where.$this->_group.' limit 1';
        $num = self::fetch($sql);
        $this->_sql = $sql;
        return $num['num'] ? $num['num'] : 0;
    }
    /**
     * 删除数据
     * @access public
     * @param mixed $order 字段 string
     * @author zly 08/30
     * @return obj
     */
    public function del(){

        $sql = "delete from ".$this->_table." where ".$this->_where.' '.$this->_group.$this->_order.' '.$this->_limit;
        try
        {
            $this->_sql = $sql;
            return self::$__PDO->exec($sql);
        }
        catch(PDOException $e)
        {
            throw new PDOException($e);
        }
    }
    /**
     * 更新数据
     * @access public
     * @param mixed $order 字段 string
     * @author zly 08/30
     * @return obj
     */
    public function update($data){
        $update = [];
        foreach($data as $key => $val){
            if(is_string($val)){
                $update[] = "`$key` = '$val'"; 
            }else{
                $update[] = "`$key` = $val";
            }
             
        }
        $save = implode(',',$update);
         try
        {
           if($save){
                $sql = "update ".$this->_table.' set '.$save.' where '.$this->_where.' '.$this->_order.' '.$this->_limit;
                $this->_sql = $sql;
                return self::$__PDO->exec($sql);
            }else{
                return false; 
            }
        }
        catch(PDOException $e)
        {
            throw new PDOException($e);
        }
        
       
    }
    /**
     * 添加数据
     * @access public
     * @param mixed $data array
     * @author zly 08/30
     * @return obj
     */
    public function add($data){
        $keys = [];
        $value = [];
        foreach($data as $key => $val){
            $key = str_replace(':', "", $key);
            $keys[] = "`$key`";
            if(is_string($val)){
                $value[] = "'$val'";
            }else{
               $value[] = $val; 
            }
            
        }
        $keys = implode(',',$keys);
        $value = implode(',', $value);
        $sql = "insert into ".$this->_table."($keys) values "."($value)";
        try
        {
           $res =  self::$__PDO->exec($sql);
           $this->_sql = $sql;
           if($res){
                return self::$__PDO->lastInsertId();
           }else{
                return $res;
           }
           
        }
        catch(PDOException $e)
        {
            throw new PDOException($e);
        }
         
    }
    /**
     * 连表查询
     * @access public
     * @param mixed $table user as b on a.uid = b.id
     * @param mixed $join left right
     * @author zly 08/30
     * @return obj
     */
    public function join($table,$join='left'){
        $this->_join .= " ".$join." join ".$table;
        return $this;
    }
    /**
     * 添加表名
     * @access public
     * @param mixed $alias
     * @author zly 08/30
     * @return obj
     */
    public function table($table){
        $this->_table = $table;
        return $this;
    }
    /**
     * 支持 or查询
     * @access public
     * @param mixed $params 支持数组和string 类型的查询
     * @author zly 09/03
     * @return obj
     */
    public function orWhere($params){
        if(empty($params)){
            return $this;
        }
        $sql = '';
        $where = formatWhere($params,' OR ');
        $this->_where.= " AND ( ".$where." ) "; 
        return $this;
    }
    /**
     * 支持获取当个字段
     * @access public
     * @param $field_name 字段名称
     * @param $one 获取一条数据
     * @author zly 09/03
     * @return obj
     */
    public function getField($field_name,$one=true){
        $this->_field = $field_name;
        if($one){
            $data = $this->one();
            return $data[$field_name] ? $data[$field_name] : ''; 
        }else{
            $data = $this->getAll();
            return $data ? array_column($data, $field_name) : [];
        }
        
    }
    /**
     * 设置单字段增加
     * @access public
     * @param $field_name 字段名称
     * @param $value 获取一条数据
     * @author zly 11/2
     * @return obj
     */
    public function setInc($field,$value){
        $save = "`$field` = `$field` + $value";
        try
        {
           if($save){
                $sql = "update ".$this->_table.' set '.$save.' where '.$this->_where.' '.$this->_order.' '.$this->_limit;
                $this->_sql = $sql;
                return self::$__PDO->exec($sql);
            }else{
                return false; 
            }
        }
        catch(PDOException $e)
        {
            throw new PDOException($e);
        }
    }
    /**
     * 设置单字段减少
     * @access public
     * @param $field_name 字段名称
     * @param $value 获取一条数据
     * @author zly 11/2
     * @return obj
     */
    public function setDec($field,$value){
        $save = "`$field` = `$field` - $value";
        try
        {
           if($save){
                $sql = "update ".$this->_table.' set '.$save.' where '.$this->_where.' '.$this->_order.' '.$this->_limit;
                $this->_sql = $sql;
                return self::$__PDO->exec($sql);
            }else{
                return false; 
            }
        }
        catch(PDOException $e)
        {
            throw new PDOException($e);
        }
    }
    /**
     * 开启事务
     * @access public
     * @author zly 11/03
     * @return obj
     */
    public function startTrans(){
        $sql = 'START TRANSACTION';
        return self::$__PDO->exec($sql);
    }
    /**
     * 提交事务
     * @access public
     * @author zly 11/03
     * @return obj
     */
    public function commit($commit){
        $bol = true;
        foreach($commit as $val){
            if($val === false){
                $bol = false;
            }
        }
        if($bol){
            $sql = 'COMMIT';
        }else{
            $sql = 'ROLLBACK';
        }
         self::$__PDO->exec($sql);
         return $bol;
    }
}
